-- @owner: zou_jialiang050
-- @date: 2024/4/2
-- @testpoint: pipelined函数返回单列嵌套表类型

--step1:创建嵌套表类型;expect:成功
create table t_pipelined_0002(c1 integer, c2 tinyint, c3 numeric(10, 4), c4 decimal(12),
c5 number(10,4), c6 float(10), c7 bit(1), c8 dec(10,3), c9 boolean, c10 char(10),
c11 nchar(10), c12 varchar(200), c13 varchar2(20), c14 text, c15 blob, c16 date, c17 time);
INSERT INTO t_pipelined_0002 (
    c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16, c17
) VALUES (
    -12345,  -- c1
    112,      -- c2
    1234.5678,  -- c3
    123456789012,  -- c4
    -1234.5678,  -- c5
    1234.56789,  -- c6
    b'1',    -- c7
    123.456,  -- c8
    true,     -- c9
    'char10', -- c10
    'nchar10', -- c11
    'varchar200 is a long string...', --c12
    'varchar20', -- c13 varchar2(20)
    'This is a text field content.', -- c14
    NULL,     -- c15
    '2023-10-23', -- c16 date
    '12:34:56'  -- c17 time
);

create or replace type tb_type_0002_1 as table of integer;
create or replace type tb_type_0002_2 as table of tinyint;
create or replace type tb_type_0002_3 as table of float(10);
create or replace type tb_type_0002_4 as table of decimal(12);
create or replace type tb_type_0002_5 as table of char(10);
create or replace type tb_type_0002_6 as table of date;
create or replace type tb_type_0002_7 as table of time;

--step2:创建管道函数并查看返回值;expect:成功
create or replace function func_pipelined_002_1(count in number)
returns tb_type_0002_1 pipelined
as $$
declare result integer;
begin
for i in 1 .. count loop
result = (select c1 from t_pipelined_0002 where c1 = -12345);
pipe row(result);
end loop;
return;
end;
$$ language plpgsql;
/

create or replace function func_pipelined_002_2(count in number)
returns tb_type_0002_2 pipelined
as $$
declare result tinyint;
begin
for i in 1 .. count loop
result = (select c2 from t_pipelined_0002 where c2 = 112);
pipe row(result);
end loop;
return;
end;
$$ language plpgsql;
/

create or replace function func_pipelined_002_3(count in number)
returns tb_type_0002_3 pipelined
as $$
declare result float(10);
begin
for i in 1 .. count loop
result = (select c6 from t_pipelined_0002 where c6 > 1234);
pipe row(result);
end loop;
return;
end;
$$ language plpgsql;
/

create or replace function func_pipelined_002_4(count in number)
returns tb_type_0002_4 pipelined
as $$
declare result decimal(12);
begin
for i in 1 .. count loop
result = (select c4 from t_pipelined_0002 limit 1);
pipe row(result);
end loop;
return;
end;
$$ language plpgsql;
/

create or replace function func_pipelined_002_5(count in number)
returns tb_type_0002_5 pipelined
as $$
declare result char(10);
begin
for i in 1 .. count loop
result = (select c10 from t_pipelined_0002 where c10 like '%char%');
pipe row(result);
end loop;
return;
end;
$$ language plpgsql;
/

create or replace function func_pipelined_002_6(count in number)
returns tb_type_0002_6 pipelined
as $$
declare result date;
begin
for i in 1 .. count loop
result = (select c16 from t_pipelined_0002 order by c16 limit 1);
pipe row(result);
end loop;
return;
end;
$$ language plpgsql;
/

create or replace function func_pipelined_002_7(count in number)
returns tb_type_0002_7 pipelined
as $$
declare result time;
begin
for i in 1 .. count loop
result = (select c17 from t_pipelined_0002);
pipe row(result);
end loop;
return;
end;
$$ language plpgsql;
/

--step3:调用pipelined函数并查看返回类型;expect:成功
select func_pipelined_002_1(3);
select * from func_pipelined_002_2(3);
select * from table(func_pipelined_002_3(3));
select func_pipelined_002_4(3);
select * from func_pipelined_002_5(3);
select * from table(func_pipelined_002_6(3));
select * from table(func_pipelined_002_7(3));

--step4:使用create table as创建表;expect:成功
drop table if exists t_pipelined_0002_8;
create table t_pipelined_0002_8 as select * from func_pipelined_002_1(1);
insert into t_pipelined_0002_8 (select * from table(func_pipelined_002_1(1)));
select * from t_pipelined_0002_8;

--step5:清理环境;expect:成功
drop table if exists t_pipelined_0002;
drop type if exists tb_type_0002_1 cascade;
drop type if exists tb_type_0002_2 cascade;
drop type if exists tb_type_0002_3 cascade;
drop type if exists tb_type_0002_4 cascade;
drop type if exists tb_type_0002_5 cascade;
drop type if exists tb_type_0002_6 cascade;
drop type if exists tb_type_0002_7 cascade;
drop function if exists func_pipelined_002_1;
drop function if exists func_pipelined_002_2;
drop function if exists func_pipelined_002_3;
drop function if exists func_pipelined_002_4;
drop function if exists func_pipelined_002_5;
drop function if exists func_pipelined_002_6;
drop function if exists func_pipelined_002_7;
drop table if exists t_pipelined_0002_8;
